/**
 * Created by Dell on 2017/5/10.
 */
'use strict';
var pg = require('../lib/pgClient');
const log = require('log4js').getLogger("models/brand");
const async = require('async');
let sqlStart = "START TRANSACTION;";
let sqlRollback = "ROLLBACK;";
let sqlCommit = "COMMIT;";
/**
 * 后台添加品牌
 * @param params
 * @param args
 * @param callback
 */
const add_sql1 = 'INSERT INTO brand (name, pid, grand, logo, "desc", status, createtime) VALUES ($1, $2, $3, $4, $5, $6, $7) RETURNING id';//添加父级
const add_sql2 = 'SELECT * FROM brand WHERE name = $1';//重名
const add_sql3 = 'INSERT INTO brand (name, pid, createtime, status, grand) VALUES ($1, $2, $3, $4, $5)';//添加二级品牌
exports.add_brand = function(params, args, callback){
    const params2 = [
        args.name
    ];
    var pid;
    pg.query(add_sql2, params2, function(err,result){//执行查询操作
        if (err) {
            callback(err, null);//报错
        } else if (result.rowCount > 0) {
            callback(err, 'exit_p')//一级重名
        } else {
            pg.connect(function (err, client, done) {
                client.query(sqlStart, function () {
                    client.query(add_sql1, params, function (err, result) {//查作者
                        if (err) {
                            log.error(err);
                            client.query(sqlRollback, function (errRollback) {
                                if (errRollback) {
                                    log.error('一级品牌添加失败');
                                }
                                done();
                                callback(err, null);//一级品牌添加失败;
                            })
                        } else if (result.rowCount == 0) {
                            log.error('一级品牌添加失败');
                            client.query(sqlRollback, function (errRollback) {
                                if (errRollback) {
                                    log.error('一级品牌添加失败');
                                }
                                done();
                                return callback(err,'fail_p');//一级品牌添加失败
                            })
                        } else {
                            pid = result.rows[0].id//一级分类id
                            let sub_brand = args.sub_brand;
                            let len = sub_brand.length;//有多少子分类
                            let a = 1;
                            for (let i = 0; i < len; i++) {
                                 const params3 = [
                                            sub_brand[i],
                                            pid,
                                            new Date(),
                                            1,
                                            2
                                        ]
                                client.query(add_sql3, params3, function (err, result) {//删除项目品牌关联
                                    if (err) {//失败
                                        log.error('添加二级报错');
                                        client.query(sqlRollback, function (errRollback) {
                                            if (errRollback) {
                                                log.error('添加二级报错');
                                            }
                                            done();
                                            callback(err,null);
                                        });
                                    }else if (result.rowCount == 0) {//失败
                                        log.error(err);
                                        client.query(sqlRollback, function (errRollback) {
                                            if (errRollback) {
                                                log.error('/添加二级失败');
                                            }
                                            done();
                                            callback(err, 'fail_s')//添加二级失败
                                        });
                                    } else {
                                        client.query(sqlCommit, function (errCommit) {
                                            if (errCommit) {
                                                client.query(sqlRollback, function (errRollback) {
                                                    if (errRollback) {
                                                        log.error('回滚失败');
                                                    }
                                                    done();
                                                    callback(err, null);
                                                });
                                            } else {
                                                a++;
                                                if (a == len) {
                                                    done();
                                                    callback(err, result)//成功
                                                }
                                            }
                                        });
                                    }
                                });
                            }
                        }
                    });
                });
            })
        };
    });
};
/**
 * 后台品牌多条件查询
 * @param params
 * @param args
 * @param callback
 */
exports.list_b = function(params, args, callback){
    let sql = 'SELECT * FROM brand WHERE grand = $1';//简单的查询（一级品牌）
    let number = 2;
    if(args.status&&args.status!='all'){
        sql +=' AND status = $'+number;
        number++;
    }
    if(args.keyword){
        sql += ' AND ( name like $'+number+' OR "desc" like $'+number+' )';
        number++;
    }
    sql += ' ORDER BY createtime DESC LIMIT $'+number+' OFFSET $'+(number+1);
    pg.query(sql, params, function(err, result){//执行查询的sql语句
        if(err){
            callback (err, null);//报错
        }else{
            callback (err, result);
        }
    });
};

/**
 * 后台品牌多条件查询数据统计
 * @param params
 * @param args
 * @param callback
 */
exports.count_b = function(params, args, callback){
    let sql = 'SELECT count(*) FROM brand WHERE grand = $1';//简单的查询（一级品牌）
    let number = 2;
    if(args.status&&args.status!='all'){
        sql +=' AND status = $'+number;
        number++;
    }
    if(args.keyword){
        sql += ' AND ( name like $'+number+' OR "desc" like $'+number+' )';
        number++;
    }
    pg.query(sql, params, function(err, result){//执行查询的sql语句
        if(err){
            callback (err, null);//报错
        }else{
            callback (err, result);
        }
    });
};
/**
 * 后台一级品牌的子品牌
 * @param params
 * @param callback
 */
const sub_sql1  = 'SELECT id FROM brand WHERE grand = $1 AND id = $2';//查父级品牌
const sub_sql2 = 'SELECT b.name, b.id, b.pid, a.name as pname, a.logo as plogo  FROM brand a,brand b WHERE a.id= b.pid AND b.grand = $1 AND b.pid = $2';//查子品牌
exports.sub_brand = function(params1, params2, callback){
    pg.query(sub_sql1, params1, function(err, result){//查一级品牌
        if(err){//报错
            callback (err, null);
        }else if(result.rowCount == 0){
            callback (err, 'null')//一级品牌不存在
        }else {
            pg.query(sub_sql2, params2, function(err, result){//查询子品牌
                if(err){
                    callback (err, null);//失败
                }else {
                    callback (err, result);//成功
                }
            });
        }
    });
};
/**
 * 编辑社区时，社区详情
 * * @param params
 * @param callback
 */
const info_sql1 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2';//查询对应的品牌(必须是一级品牌)
exports.brand_info = function(params, callback){
    pg.query(info_sql1, params, function(err, result){//执行查询操作
        if(err){
            callback (err, null);//报错
        }else{
            callback (err, result);//查出结果
        }
    });
};
/**
 * 后台编辑一级品牌
 * @param params
 * @param args
 * @param callback
 */
const upd_sql1 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2';//查找是否有这个一级品牌
const upd_sql2= 'SELECT * FROM brand WHERE name = $1 AND grand = $2 AND id != $3';//查找除了这个品牌之外，是否有重名的一级品牌
const upd_sql3 = 'UPDATE brand SET name = $1, logo = $2, "desc" = $3, status = $4 WHERE id = $5 AND grand = $6';//编辑对应一级品牌的信息
const upd_sql4 = 'UPDATE brand SET status = $1 WHERE pid = $2 AND grand = $3';//修改子品牌的状态
exports.upd_brand = function(params, args, callback){
    const params1 = [
        args.brandid,//品牌id
        1//一级品牌
    ];
    const params2 = [
        args.name,//品牌名称
        1,//一级品牌
        args.brandid//除了这个品牌之外
    ];
    const params4 = [
        args.status,//要修改的状态
        args.brandid,
        2
    ];
    pg.query(upd_sql1, params1, function(err, result){//查询一级品牌
        if(err){
            callback (err, null);//报错
        }else if(result.rowCount==0){
            callback (err, 'null');//没有这个一级品牌
        }else {
            pg.query(upd_sql2, params2, function(err, result){//查询是否有重名的
                if(err){
                    callback (err, null);//报错
                }else if(result.rowCount >0){
                    callback (err, 'exit_name');//有重名的一级品牌
                }else {
                    pg.connect(function (err, client, done) {
                        client.query(sqlStart, function () {
                            client.query(upd_sql3, params, function (err, result) {//修改一级品牌
                                if (err) {
                                    log.error(err);
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('一级品牌修改报错');
                                        }
                                        done();
                                        callback(err, null);//一级品牌修改报错;
                                    })
                                } else if (result.rowCount == 0) {
                                    log.error('一级品牌修改失败');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('一级品牌修改失败');
                                        }
                                        done();
                                        return callback(err, 'fail');//一级品牌修改失败
                                    })
                                } else {
                                    client.query(upd_sql4, params4, function (err, result) {//修改子级品牌状态
                                        if (err) {
                                            log.error(err);
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('修改子级品牌状态报错');
                                                }
                                                done();
                                                callback(err, null);//修改子级品牌状态报错;
                                            })
                                        } else if (result.rowCount == 0) {
                                            log.error('修改子级品牌状态失败');
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('修改子级品牌状态失败');
                                                }
                                                done();
                                                return callback(err, 'fail');//修改子级品牌状态失败
                                            })
                                        } else {
                                            client.query(sqlCommit, function (errCommit) {//提交
                                                if (errCommit) {
                                                    client.query(sqlRollback, function (errRollback) {
                                                        if (errRollback) {
                                                            log.error('回滚失败');
                                                        }
                                                        done();
                                                        callback(err, null);
                                                    });
                                                } else {
                                                    done();
                                                    callback(err, result)//成功
                                                }
                                            });
                                        }
                                    });
                                }
                            });
                        });
                    });
                }
            });
        }
    });
};
/**
 * 后台编辑一级品牌
 * @param params
 * @param args
 * @param callback
 */
exports.upd_status = function(params, args, callback){
    let ids = args.brandids;//ids 是数组
    let theids = "'"+ids.join("','")+"'";//将数组ids整理成字符串
    const params1 = [
        1//一级品牌
    ];
    const params3 = [
        args.status,//要修改的状态
        2
    ];
    const sta_sql1 = 'SELECT * FROM brand WHERE id IN ('+theids+') AND grand = $1';//查找是否有这个一级品牌
    const sta_sql2 = 'UPDATE brand SET status = $1 WHERE id IN ('+theids+') AND grand = $2';//编辑对应一级品牌的信息
    const sta_sql3 = 'UPDATE brand SET status = $1 WHERE pid IN ('+theids+') AND grand = $2';//修改子品牌的状态
    pg.query(sta_sql1, params1, function(err, result){//查询一级品牌
        if(err){
            callback (err, null);//报错
        }else if(result.rowCount==0){
            callback (err, 'null');//没有这个一级品牌
        }else {
            pg.connect(function (err, client, done) {
                client.query(sqlStart, function () {
                    client.query(sta_sql2, params, function (err, result) {//修改一级品牌
                        if (err) {
                            log.error(err);
                            client.query(sqlRollback, function (errRollback) {
                                if (errRollback) {
                                    log.error('一级品牌修改报错');
                                }
                                done();
                                callback(err, null);//一级品牌修改报错;
                            })
                        } else if (result.rowCount == 0) {
                            log.error('一级品牌修改失败');
                            client.query(sqlRollback, function (errRollback) {
                                if (errRollback) {
                                    log.error('一级品牌修改失败');
                                }
                                done();
                                return callback(err, 'fail');//一级品牌修改失败
                            })
                        } else {
                            client.query(sta_sql3, params3, function (err, result) {//修改子级品牌状态
                                if (err) {
                                    log.error(err);
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('修改子级品牌状态报错');
                                        }
                                        done();
                                        callback(err, null);//修改子级品牌状态报错;
                                    })
                                } else if (result.rowCount == 0) {
                                    log.error('修改子级品牌状态失败');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('修改子级品牌状态失败');
                                        }
                                        done();
                                        return callback(err, 'fail');//修改子级品牌状态失败
                                    })
                                } else {
                                    client.query(sqlCommit, function (errCommit) {//提交
                                        if (errCommit) {
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('回滚失败');
                                                }
                                                done();
                                                callback(err, null);
                                            });
                                        } else {
                                            done();
                                            callback(err, result)//成功
                                        }
                                    });
                                }
                            });
                        }
                    });
                });
            });
        }
    });
};
/**
 * 后台编辑二级分类名
 * @param params
 * @param args
 * @param callback
 */
const subname_sql1= 'SELECT * FROM brand WHERE name = $1 AND id != $2 AND pid = $3 AND grand = $4';//查找该品牌名是否已经被占用
const subname_sql2 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2 AND pid = $3';//是否有这个二级品牌
const subname_sql3 = 'UPDATE brand SET name = $1 WHERE id = $2 AND pid = $3 AND grand = $4';//修改二级分类
exports.upd_sub = function(params, args, callback){
    const params2 = [
        args.brandid,
        2,
        args.pid
    ];
    pg.query(subname_sql1, params, function(err, result){//查找该父级品牌中是否已经有这个品牌
        if(err){
            callback (err, null);//报错
        }else if(result.rowCount > 0 ){
            callback (err, 'exit_name');//已经存在
        }else{
            pg.query(subname_sql2, params2, function(err, result){//查找是否有这个二级品牌
                if(err){
                    callback (err, null);//报错
                }else if(result.rowCount == 0){
                    callback (err, 'null');//没有这个二级品牌
                }else{
                    pg.query(subname_sql3, params, function(err, result){//执行编辑操作
                        if(err){
                            callback (err, null);//报错
                        }else if(result.rowCount == 0){
                            callback (err, 'fail');//编辑失败
                        }else {
                            callback (err, result);//成功
                        }
                    });
                }
            });
        }
    });
};
/**
 * 后台批量删除品牌
 * @param args
 * @param callback
 */
exports.remove = function(args, callback){
    let ids = args.brandids;//ids 是数组
    let theids = "'"+ids.join("','")+"'";//将数组ids整理成字符串
    const sql1 = 'SELECT * FROM brand WHERE id in ('+theids+') AND grand = $1 ';//查询是否含有二级品牌
    const sql2 = 'DELETE FROM brand WHERE id in ('+theids+') AND grand = $1';//删除一级品牌
    const sql3 = 'DELETE FROM brand WHERE pid in ('+theids+') AND grand = $1';//删除二级品牌
    const sql4 = 'DELETE FROM brandlogs WHERE brandid in ('+theids+')';//删除与图片的关联
    const sql5 = 'DELETE FROM brand_project WHERE brandid in ('+theids+')';//删除与项目的关联
    const params1 = [
        2
    ];
    const params2 = [
        1
    ];
    pg.query(sql1, params1, function(err, result){//查询是否含有二级品牌
        if(err){
            callback (err,null);//报错
        }else if(result.rowCount > 0){
            callback (err, 'second');//有二级分类
        }else {
            pg.connect(function (err, client, done) {
                client.query(sqlStart, function () {
                    client.query(sql2, params2, function (err, result) {//删除一级品牌
                        if(err){
                            log.error('删除一级品牌报错');
                            client.query(sqlRollback, function(errRollback){
                                if(errRollback){
                                    log.error('回滚失败');
                                }
                                done();
                                callback (err,null);
                            })
                        }else if(result.rowCount == 0){
                            log.error('删除一级品牌失败');
                            client.query(sqlRollback, function(errRollback){
                                if(errRollback){
                                    log.error('回滚失败');
                                }
                                done();
                                callback (err, 'fail');
                            })
                        }else {
                            client.query(sql3, params1, function (err, result) {//删除二级品牌
                                if (err) {
                                    log.error('删除二级品牌报错');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('回滚失败');
                                        }
                                        done();
                                        callback(err, null);
                                    })
                                } else if (result.rowCount == 0) {
                                    log.error('删除二级品牌失败');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('回滚失败');
                                        }
                                        done();
                                        callback(err, 'fail');
                                    })
                                } else {
                                    client.query(sql4, [], function (err, result) {//删除与图片的关联
                                        if(err){
                                            log.error('删除与图片的关联报错');
                                            client.query(sqlRollback, function(errRollback){
                                                if(errRollback){
                                                    log.error('回滚失败');
                                                }
                                                done();
                                                callback (err,null);
                                            })
                                        }else {
                                            client.query(sql5, [], function (err, result) {//删除与项目的关联
                                                if (err) {
                                                    log.error('删除与项目的关联报错');
                                                    client.query(sqlRollback, function (errRollback) {
                                                        if (errRollback) {
                                                            log.error('回滚失败');
                                                        }
                                                        done();
                                                        callback(err, null);
                                                    })
                                                }else {
                                                    client.query(sqlCommit, function (errCommit) {
                                                        if (errCommit) {
                                                            client.query(sqlRollback, function (errRollback) {
                                                                if (errRollback) {
                                                                    log.error('回滚失败');
                                                                }
                                                                done();
                                                                callback(err, null);
                                                            });
                                                        } else {
                                                            done();
                                                            callback(err, result);//成功
                                                        }
                                                    })
                                                }
                                            });
                                        }
                                    });
                                }
                            });
                        }
                    });
                });
            });
        }
    });
};
/**
 *  后台品牌关联图片（已关联和未关联列表）
 * @param params
 * @param args
 * @param callback
 */
const picture_list_sql1 = 'SELECT * FROM brandlogs WHERE brandid = $1';//已关联图片记录
exports.picture_list = function(params, args, callback){
    const params1 = [
        args.brandid
    ];
    let ids = [];
    let sql = 'SELECT id, name, imgurl FROM picture WHERE id';
    pg.query(picture_list_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.pictureid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            if(args.type == 'related'){//已关联
                sql += ' in ('+theids+') ORDER BY pushtime DESC LIMIT $1 OFFSET $2';
            }else{//未关联
                sql += ' not in ('+theids+') AND status = $3 AND del = $4 ORDER BY pushtime DESC LIMIT $1 OFFSET $2';
            }
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    callback (err, result);
                }
            });
        }
    });
};
/**
 *  后台品牌关联图片（已关联和未关联列表）
 * @param params
 * @param args
 * @param callback
 */
const picture_count_sql1 = 'SELECT * FROM brandlogs WHERE brandid = $1';//已关联图片记录
exports.picture_count = function(params, args, callback){
    const params1 = [
        args.brandid
    ];
    let ids = [];
    let sql = 'SELECT count(*) FROM picture WHERE id';
    pg.query(picture_count_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.pictureid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            if(args.type == 'related'){//已关联
                sql += ' in ('+theids+')';
            }else{//未关联
                sql += ' not in ('+theids+') AND status = $1 AND del = $2 ';
            }
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    callback (err, result);
                }
            });
        }
    });
};
/**
 *后台设置图片和品牌关联
 * @param args
 * @param callback
 */
const relate_sql1 = 'SELECT * FROM picture WHERE id = $1 AND status = $2 AND del = $3';//查正常的图片
const relate_sql2 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2 AND status = $3';//查正常的二级品牌
const relate_sql3 = 'SELECT * FROM brandlogs WHERE pictureid = $1 AND brandid = $2';//查找是否已经添加过关联
const relate_sql4 = 'SELECT * FROM brandlogs WHERE pictureid = $1';//查找图片是否已经添加关联超出上限
const relate_sql5 = 'INSERT INTO brandlogs (pictureid, brandid, createtime) VALUES ($1, $2, $3)';//后台添加关联记录
exports.picture_relate = function(args, callback){
    const params1 = [
        args.pictureid,//id
        1,//正常状态
        1//未删除
    ];
    const params2 = [
        args.brandid,//id
        2,//二级品牌
        1//正常状态
    ];
    const params3 = [
        args.pictureid,//图片id
        args.brandid //品牌id
    ];
    const params4 = [
        args.pictureid//图片id
    ];
    const params5 = [
        args.pictureid,//图片id
        args.brandid,//品牌id
        new Date()//创建时间
    ];
    let info = [];
    pg.query(relate_sql1, params1, function(err, result){//查正常的图片
        if(err){
            callback (err, result);//报错
        }else if(result.rowCount==0){
            callback (err, 'null_picture');//没有图片
        }else {
            info.push(result.rows[0].name);//图片名
            pg.query(relate_sql2, params2, function(err, result){//查正常的二级品牌
                if(err){
                    callback (err, result);//报错
                }else if(result.rowCount==0){
                    callback (err, 'null_brand');//没有品牌
                }else {
                    info.push(result.rows[0].name);//品牌名
                    pg.query(relate_sql3, params3, function(err, result){//查找是否已经添加过关联
                        if(err){
                            callback (err, result);//报错
                        }else if(result.rowCount>1){
                            callback (err, 'exit_relate');//已经添加过关联
                        }else {
                            pg.query(relate_sql4, params4, function(err, result){//查找图片是否已经添加关联超出上限
                                if(err){
                                    callback (err, result);//报错
                                }else if(result.rowCount>5){
                                    callback (err, 'too_munch_picture');//已经添加关联达到上限
                                }else {
                                    pg.query(relate_sql5, params5, function(err, result){//查找图片是否已经添加关联超出上限
                                        if(err){
                                            callback (err, result);//报错
                                        }else if(result.rowCount==0){
                                            callback (err, 'fail');//失败
                                        }else {
                                            callback (err, info);//成功
                                        }
                                    });
                                }
                            });
                        }
                    });
                }
            });
        }
    });
};
/**
 *后台设置图片和品牌关联
 * @param args
 * @param callback
 */
const unrelate_sql1 = 'SELECT * FROM picture WHERE id = $1 AND del = $2';//查正常的图片
const unrelate_sql2 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2';//查正常的二级品牌
const unrelate_sql3 = 'SELECT * FROM brandlogs WHERE pictureid = $1 AND brandid = $2';//查找是否已经添加过关联
const unrelate_sql5 = 'DELETE FROM brandlogs WHERE pictureid = $1 AND brandid = $2';//后台添加关联记录
exports.picture_unrelate = function(args, callback){
    const params1 = [
        args.pictureid,//id
        1
    ];
    const params2 = [
        args.brandid,//id
        2//二级品牌
    ];
    const params5 = [
        args.pictureid,//图片id
        args.brandid//品牌id
    ];
    let info = [];
    pg.query(unrelate_sql1, params1, function(err, result){//查正常的图片
        if(err){
            callback (err, result);//报错
        }else if(result.rowCount==0){
            callback (err, 'null_picture');//没有图片
        }else {
            info.push(result.rows[0].name);//图片名
            pg.query(unrelate_sql2, params2, function(err, result){//查正常的二级品牌
                if(err){
                    callback (err, result);//报错
                }else if(result.rowCount==0){
                    callback (err, 'null_brand');//没有品牌
                }else {
                    info.push(result.rows[0].name);//品牌名
                    pg.query(unrelate_sql3, params5, function(err, result){//查找是否已经添加过关联
                        if(err){
                            callback (err, result);//报错
                        }else if(result.rowCount==0){
                            callback (err, 'exit_relate');//未添加过关联
                        }else {
                            pg.query(unrelate_sql5, params5, function(err, result){//删除
                                if(err){
                                    callback (err, result);//报错
                                }else if(result.rowCount==0){
                                    callback (err, 'fail');//失败
                                }else {
                                    callback (err, info);//成功
                                }
                            });
                        }
                    });
                }
            });
        }
    });
};
//
// /**
//  * 图片关联项目列表
//  * @param args
//  * @param callback
//  */
// const relate_list_picture_sql1 = 'SELECT projectid FROM projestlogs WHERE pictureid = $1';//查已经关联图片的项目的id
// exports.relate_list_picture = function(args, params, callback){
//     let params1 = [
//         args.pictureid
//     ];
//     let ids = [];
//     let sql = 'SELECT * FROM project WHERE id';
//     pg.query(relate_list_picture_sql1, params1, function(err, result){
//         if(err){
//             callback (err, null);
//         }else{
//             if(result.rowCount == 0){
//                 ids.push(0);
//             }else {
//                 let rows = result.rows;
//                 rows.forEach(function(row){
//                     ids.push(row.projectid)
//                 })
//             }
//             let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
//             log.debug(theids);
//             if(args.type == 'related'){//已关联
//                 sql += ' in ('+theids+') ORDER BY createtime DESC LIMIT $1 OFFSET $2';
//             }else{//未关联
//                 sql += ' not in ('+theids+') AND status = $3 ORDER BY createtime DESC LIMIT $1 OFFSET $2';
//             }
//             pg.query(sql, params, function(err, result){//执行查询操作
//                 if(err){
//                     callback (err, null);
//                 }else {
//                     callback (err, result);
//                 }
//             });
//         }
//     });
// };
// /**
//  * 图片关联项目列表数据统计
//  * @param args
//  * @param callback
//  */
// const relate_count_picture_sql1 = 'SELECT projectid FROM projestlogs WHERE pictureid = $1';//查已经关联图片的项目的id
// exports.relate_count_picture = function(args, params,  callback){
//     let params1 = [
//         args.pictureid
//     ];
//     let ids = [];
//     let sql = 'SELECT count(*) FROM project WHERE id';
//     pg.query(relate_count_picture_sql1, params1, function(err, result){
//         if(err){
//             callback (err, null);
//         }else{
//             if(result.rowCount == 0){
//                 ids.push(0);
//             }else {
//                 let rows = result.rows;
//                 rows.forEach(function(row){
//                     ids.push(row.projectid)
//                 })
//             }
//             let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
//             log.debug(theids);
//             if(args.type == 'related'){//已关联
//                 sql += ' in ('+theids+')';
//             }else{//未关联
//                 sql += ' not in ('+theids+') AND status = $1';
//             }
//             pg.query(sql, params, function(err, result){//执行查询操作
//                 if(err){
//                     callback (err, null);
//                 }else {
//                     callback (err, result);
//                 }
//             });
//         }
//     });
// };

/**
 * 图片关联项目列表
 * @param args
 * @param callback
 */
const relate_list_brand_sql1 = 'SELECT brandid FROM brand_project WHERE projectid = $1';//查已经关联图片的项目的id
exports.relate_list_brand = function(args, params, callback){
    let params1 = [
        args.projectid
    ];
    let ids = [];
    let sql = 'SELECT b.name, b.id, b.pid, a.name as pname, a.logo as plogo  FROM brand a,brand b WHERE a.id= b.pid AND b.id';
    pg.query(relate_list_brand_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.brandid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            if(args.type == 'related'){//已关联
                sql += ' in ('+theids+') AND b.grand = $3 ORDER BY b.createtime DESC LIMIT $1 OFFSET $2';
            }else{//未关联
                sql += ' not in ('+theids+') AND b.status = $4 AND b.grand = $3 ORDER BY b.createtime DESC LIMIT $1 OFFSET $2';
            }
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    callback (err, result);
                }
            });
        }
    });
};
/**
 * 图片关联项目列表数据统计
 * @param args
 * @param callback
 */
const relate_count_brand_sql1 = 'SELECT brandid FROM brand_project WHERE projectid = $1';//查已经关联图片的项目的id
exports.relate_count_brand = function(args, params,  callback){
    let params1 = [
        args.projectid
    ];
    let ids = [];
    let sql = 'SELECT count(*) FROM brand WHERE id';
    pg.query(relate_count_brand_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.brandid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            if(args.type == 'related'){//已关联
                sql += ' in ('+theids+') AND grand = $1';
            }else{//未关联
                sql += ' not in ('+theids+') AND status = $2 AND grand = $1';
            }
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    callback (err, result);
                }
            });
        }
    });
};



/**
 * 图片关联项目列表
 * @param args
 * @param callback
 */
const relate_list_p_sql1 = 'SELECT brandid FROM brandlogs WHERE pictureid = $1';//查已经关联图片的项目的id
exports.relate_list_picture = function(args, params, callback){
    let params1 = [
        args.pictureid
    ];
    let ids = [];
    let sql = 'SELECT b.name, b.id, b.pid, a.name as pname, a.logo as plogo  FROM brand a,brand b WHERE a.id= b.pid AND b.id';
    pg.query(relate_list_p_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.brandid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            if(args.type == 'related'){//已关联
                sql += ' in ('+theids+') AND b.grand = $3 ORDER BY b.createtime DESC LIMIT $1 OFFSET $2';
            }else{//未关联
                sql += ' not in ('+theids+') AND b.status = $4 AND b.grand = $3 ORDER BY b.createtime DESC LIMIT $1 OFFSET $2';
            }
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    callback (err, result);
                }
            });
        }
    });
};

/**
 * 图片关联项目列表
 * @param args
 * @param callback
 */
const relate_list_picture_app_sql1 = 'SELECT pictureid FROM brandlogs WHERE brandid = $1';//查已经关联图片的项目的id
const p_sql4 = 'SELECT * FROM wantlogs WHERE userid = $1 AND type = $2 AND pid = $3';
const p_sql5 = 'SELECT * FROM ownlogs WHERE userid = $1 AND type = $2 AND pid = $3';
exports.relate_list_picture_app = function(args, params, req, callback){
    let date1 =  new Date().getTime();
    let date2 = new Date(date1 - 1000 * 60 * 60 * 6);//6小时之内
    let params1 = [
        args.brandid
    ];
    let ids = [];
    var info;
    let infos = [];
    let sql = 'SELECT * FROM picture WHERE id';
    pg.query(relate_list_picture_app_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.pictureid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            sql += ' in ('+theids+') ORDER BY pushtime DESC LIMIT $1 OFFSET $2';
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    const sqlwant = 'SELECT w.createtime, w.userid, u.headurl FROM wantlogs w, users u WHERE w.userid=u.id AND w.pid = $1 AND w.createtime > $2 AND w.type = $3 ORDER BY w.createtime DESC LIMIT 1 OFFSET 0';//查找想要记录中最后一次想要时间
                    let pictures = result.rows;
                    let len = pictures.length;
                    let a = 0;
                    if(len > 0 ){
                        pg.connect(function (err, client, done) { //获取数据库链接
                            client.query(sqlStart, function () { //开启事务
                                async.mapSeries(pictures,function (picture,callback) {
                                        let paramswant = [
                                            picture.id,
                                            date2,
                                            1
                                        ];
                                        log.debug(paramswant);
                                        const params4 = [
                                            req.user.id,
                                            1,
                                            picture.id
                                        ]
                                        client.query(sqlwant, paramswant, function (err, result) {//想要记录
                                            if (err) {
                                                callback(err, result);
                                            } else if (result.rowCount == 0) {
                                                let last_time = null;
                                                let headurl = null;
                                                client.query(p_sql4, params4, function (err, result) {//想要人数
                                                    if (err) {
                                                        callback(err, result);
                                                    } else {
                                                        let u_want = 0;
                                                        u_want = result.rowCount;
                                                        client.query(p_sql5, params4, function (err, result) {//拥有人数
                                                            if (err) {
                                                                callback(err, result);
                                                            } else {
                                                                let u_own = result.rowCount;
                                                                info = {
                                                                    id: picture.id,
                                                                    name: picture.name,
                                                                    imgurl: picture.imgurl,
                                                                    desc: picture.desc,
                                                                    want: picture.want,
                                                                    own: picture.own,
                                                                    point: picture.point,
                                                                    author: picture.author,
                                                                    pushtime: picture.pushtime,
                                                                    username: picture.username,
                                                                    headurl: headurl,
                                                                    last_time: last_time,
                                                                    u_want: u_want,
                                                                    u_own: u_own
                                                                };
                                                                a++;
                                                                infos.push(info);
                                                                callback(err, info);//结果
                                                            }
                                                        })
                                                    }
                                                })
                                            } else {
                                                let last_time = result.rows[0].createtime;
                                                let headurl = result.rows[0].headurl;
                                                client.query(p_sql4, params4, function (err, result) {//想要人数
                                                    if (err) {
                                                        callback(err, result);
                                                    } else {
                                                        let u_want = 0;
                                                        u_want = result.rowCount;
                                                        client.query(p_sql5, params4, function (err, result) {//拥有人数
                                                            if (err) {
                                                                callback(err, result);
                                                            } else {
                                                                let u_own = result.rowCount;
                                                                info = {
                                                                    id: picture.id,
                                                                    name: picture.name,
                                                                    imgurl: picture.imgurl,
                                                                    desc: picture.desc,
                                                                    want: picture.want,
                                                                    own: picture.own,
                                                                    point: picture.point,
                                                                    author: picture.author,
                                                                    pushtime: picture.pushtime,
                                                                    username: picture.username,
                                                                    headurl: headurl,
                                                                    last_time: last_time,
                                                                    u_want: u_want,
                                                                    u_own: u_own
                                                                };
                                                                a++;
                                                                infos.push(info);
                                                                callback(err, info);//结果
                                                            }
                                                        })
                                                    }
                                                })
                                            }
                                        });
                                    },
                                    function (err, n) { //循环完毕提交事务
                                        if (err) {
                                            log.error(err);
                                            log.error('回滚.............');
                                            client.query(sqlRollback, function (errRollback) {
                                                if (errRollback) {
                                                    log.error('回滚失败');
                                                    log.error(errRollback);
                                                }
                                                done();
                                                callback(errRollback, null);
                                            });
                                        } else {
                                            log.debug('提交.............');
                                            client.query(sqlCommit, function (errCommit) {
                                                if (errCommit) {
                                                    client.query(sqlRollback, function (errRollback) {
                                                        if (errRollback) {
                                                            console.log('回滚失败');
                                                            log.error(errRollback);
                                                        }
                                                        done(); //关闭链接
                                                        callback(errCommit, infos);
                                                    });
                                                } else {
                                                    done();
                                                    callback(errCommit, infos);
                                                }

                                            });
                                        }
                                    })
                            })
                        })

                    }else {
                        callback (err, infos);//没作品
                    }

                }
            });
        }
    });
};

/**
 * 图片关联项目列表数据统计
 * @param args
 * @param callback
 */
const relate_count_p_sql1 = 'SELECT brandid FROM brandlogs WHERE pictureid = $1';//查已经关联图片的项目的id
exports.relate_count_picture = function(args, params,  callback){
    let params1 = [
        args.pictureid
    ];
    let ids = [];
    let sql = 'SELECT count(*) FROM brand a,brand b WHERE a.id= b.pid AND b.id';
    pg.query(relate_count_p_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else{
            if(result.rowCount == 0){
                ids.push(0);
            }else {
                let rows = result.rows;
                rows.forEach(function(row){
                    ids.push(row.brandid)
                })
            }
            let theids = "'"+ids.join("','")+"'";//将数组转化为字符串
            log.debug(theids);
            if(args.type == 'related'){//已关联
                sql += ' in ('+theids+') AND b.grand = $1';
            }else{//未关联
                sql += ' not in ('+theids+') AND b.status = $2 AND b.grand = $1';
            }
            pg.query(sql, params, function(err, result){//执行查询操作
                if(err){
                    callback (err, null);
                }else {
                    callback (err, result);
                }
            });
        }
    });
};
/**
 * 用户关注品牌
 * @param params
 * @param req
 * @param callback
 */
const attr_sql1 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2 AND status = $3';//查一级品牌
const attr_sql2 = 'UPDATE brand SET fans = $1 WHERE id = $2';//修改一级品牌的关注数
const attr_sql3 = 'INSERT INTO brand_fans (userid, brandid, createtime) VALUES ($1, $2, $3)';//添加记录表
const attr_sql4 = 'SELECT * FROM brand_fans WHERE userid = $1 AND brandid = $2';//查记录表
exports.attracted = function(params, req, callback){
    const params1 = [
        params[0],
        1,//一级品牌
        1
    ];
    const params3 = [
        req.user.id,
        params[0],
        new Date()
    ];
    const params4 = [
        req.user.id,
        params[0]
    ];
    pg.query(attr_sql1, params1, function(err, result){//执行查询一级分类的操作
        if(err){
            callback (err, null);
        }else if(result.rowCount == 0){
            callback (err, 'null');//没有这个一级分类
        }else {
            let brand = result.rows[0];
            pg.query(attr_sql4, params4, function (err, result) {//执行查询一级分类的操作
                if (err) {
                    callback(err, null);
                } else if (result.rowCount > 0) {
                    callback(err, 'exit_logs');//已经添加关注
                } else {
                    const params2 = [
                        Number(brand.fans?brand.fans:0) + 1,
                        params[0]
                    ];
                    pg.connect(function (err, client, done) {
                        client.query(sqlStart, function () {
                            client.query(attr_sql2, params2, function (err, result) {//修改一级品牌
                                if (err||result.rowCount == 0) {
                                    log.error('一级品牌修改失败');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('一级品牌修改失败');
                                        }
                                        done();
                                        callback(err, null);//一级品牌修改失败;
                                    })
                                } else {
                                    client.query(attr_sql3, params3, function(err, result){
                                        if(err||result.rowCount == 0){
                                            log.error('添加记录失败');
                                            client.query(sqlRollBack, function(errRollback){
                                                if(errRollback){
                                                    log.error('添加记录失败');
                                                }
                                                done();
                                                callback (err, null);//添加记录失败
                                            })
                                        }else {
                                            client.query(attr_sql1, params1, function(err, result){
                                                if(err||result.rowCount == 0){
                                                    log.error('添加记录失败');
                                                    client.query(sqlRollBack, function(errRollback){
                                                        if(errRollback){
                                                            log.error('添加记录失败');
                                                        }
                                                        done();
                                                        callback (err, null);//添加记录失败
                                                    })
                                                }else {
                                                    let fans = result.rows[0].fans;
                                                    client.query(sqlCommit, function (errCommit) {//结束进程
                                                        if (errCommit) {
                                                            client.query(sqlRollback, function (errRollback) {
                                                                if (errRollback) {
                                                                    log.error('回滚失败');
                                                                }
                                                                done();
                                                                callback(err, null);
                                                            });
                                                        } else {
                                                            done();
                                                            callback(err, fans);//成功
                                                        }
                                                    })
                                                }
                                            })
                                        }
                                    })

                                }
                            })
                        })
                    })
                }
            })

        }
    })
};
/**
 * 用户关注品牌
 * @param params
 * @param req
 * @param callback
 */
const unattr_sql1 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2 AND status = $3';//查一级品牌
const unattr_sql2 = 'UPDATE brand SET fans = $1 WHERE id = $2';//修改一级品牌的关注数
const unattr_sql3 = 'DELETE FROM brand_fans WHERE id = $1';//添加记录表
const unattr_sql4 = 'SELECT * FROM brand_fans WHERE userid = $1 AND brandid = $2';//查记录表
exports.unattracte = function(params, req, callback){
    const params1 = [
        params[0],
        1,//一级品牌
        1
    ];
    const params4 = [
        req.user.id,
        params[0]
    ]
    pg.query(unattr_sql1, params1, function(err, result){//执行查询一级分类的操作
        if(err){
            callback (err, null);
        }else if(result.rowCount == 0){
            callback (err, 'null');//没有这个一级分类
        }else {
            let brand = result.rows[0];
            const params2 = [
                Number(brand.fans?brand.fans:0) - 1,
                params[0]
            ];
            pg.query(unattr_sql4, params4, function(err, result) {//执行查询一级分类的操作
                if (err) {
                    callback(err, null);
                } else if (result.rowCount == 0) {
                    callback(err, 'null_logs');//没有添加关注
                } else {
                    let log = result.rows[0];
                    const params3 = [
                        log.id
                    ];
                    pg.connect(function (err, client, done) {
                        client.query(sqlStart, function () {
                            client.query(unattr_sql2, params2, function (err, result) {//修改一级品牌
                                if (err||result.rowCount == 0) {
                                    log.error('一级品牌修改失败');
                                    client.query(sqlRollback, function (errRollback) {
                                        if (errRollback) {
                                            log.error('一级品牌修改失败');
                                        }
                                        done();
                                        callback(err, null);//一级品牌修改失败;
                                    })
                                } else {
                                    client.query(unattr_sql3, params3, function(err, result){
                                        if(err||result.rowCount == 0){
                                            log.error('删除记录失败');
                                            client.query(sqlRollBack, function(errRollback){
                                                if(errRollback){
                                                    log.error('删除记录失败');
                                                }
                                                done();
                                                callback (err, null);//添加记录失败
                                            })
                                        }else {
                                            client.query(unattr_sql1, params1, function(err, result){
                                                if(err||result.rowCount == 0){
                                                    log.error('添加记录失败');
                                                    client.query(sqlRollBack, function(errRollback){
                                                        if(errRollback){
                                                            log.error('添加记录失败');
                                                        }
                                                        done();
                                                        callback (err, null);//添加记录失败
                                                    })
                                                }else {
                                                    let fans = result.rows[0].fans;
                                                    client.query(sqlCommit, function (errCommit) {//结束进程
                                                        if (errCommit) {
                                                            client.query(sqlRollback, function (errRollback) {
                                                                if (errRollback) {
                                                                    log.error('回滚失败');
                                                                }
                                                                done();
                                                                callback(err, null);
                                                            });
                                                        } else {
                                                            done();
                                                            callback(err, fans);//成功
                                                        }
                                                    })
                                                }
                                            })
                                        }
                                    })

                                }
                            })
                        })
                    })
                }
            })
        }
    })
};
/**
 * 品牌详情
 * @param params
 * @param callback
 */
const app_info_sql1 = 'SELECT * FROM brand WHERE id = $1 AND grand = $2 AND status = $3';//查一级品牌
const app_info_sql2 = 'SELECT * FROM brand_fans WHERE brandid = $1 AND userid = $2';//查粉丝记录
exports.info = function(params, callback){
    const params1 = [
        params[0],
        1,
        1
    ];
    var fan;
    pg.query(app_info_sql1, params1, function(err, result){
        if(err){
            callback (err, null);
        }else if(result.rowCount == 0) {
            callback (err, null);
        }else {
            let brand = result.rows[0];
            pg.query(app_info_sql2, params, function(err, result){
                if(err){
                    callback (err, null);
                }else if(result.rowCount == 0) {
                   fan = 0;
                   let info = {
                       name:brand.name,
                       logo:brand.logo,
                       desc:brand.desc,
                       fans:brand.fans,
                       follow:fan
                   }
                   callback (err, info);
                }else {
                    fan = 1;
                    let info = {
                        name:brand.name,
                        logo:brand.logo,
                        desc:brand.desc,
                        fans:brand.fans,
                        follow:fan
                    }
                    callback (err, info);
                }
            })
        }
    })
}
